/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.SANPHAMDTO;
import SQL.DataProvider;
import java.util.ArrayList;
import java.sql.ResultSet;

/**
 *
 * @author NGUYEN
 */
public class SANPHAMDAO {

    public ArrayList<SANPHAMDTO> timsanphamcoloai(int loai) {
        DataProvider pro = new DataProvider();
        ArrayList<SANPHAMDTO> ds = new ArrayList<SANPHAMDTO>();
        ResultSet rs = null;
        try {
            pro.open();
            String query = "SELECT * FROM SANPHAM WHERE MaLoai =" + loai;
            rs = pro.excutequery(query);
            while (rs.next()) {
                SANPHAMDTO dto = new SANPHAMDTO();
                dto.setMasp(Integer.parseInt(rs.getString("MaSP")));
                dto.setTensp(rs.getString("TenSP"));
                dto.setDongia(Integer.parseInt(rs.getString("DonGia")));
                dto.setMaloai(Integer.parseInt(rs.getString("MaLoai")));
                dto.setSoluong(Integer.parseInt(rs.getString("SoLuong")));
                dto.setTinhtrang(rs.getString("TinhTrang"));
                ds.add(dto);
            }
            return ds;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            pro.close();
        }
        return ds;
    }

    public ArrayList<SANPHAMDTO> timsanphamcoten(String ten) {
        DataProvider pro = new DataProvider();
        ArrayList<SANPHAMDTO> ds = new ArrayList<SANPHAMDTO>();
        ResultSet rs = null;
        try {
            pro.open();
            String query = "SELECT * FROM SANPHAM WHERE TenSP LIKE '%" + ten + "%'";
            rs = pro.excutequery(query);
            while (rs.next()) {
                SANPHAMDTO dto = new SANPHAMDTO();
                dto.setMasp(Integer.parseInt(rs.getString("MaSP")));
                dto.setTensp(rs.getString("TenSP"));
                dto.setDongia(Integer.parseInt(rs.getString("DonGia")));
                dto.setMaloai(Integer.parseInt(rs.getString("MaLoai")));
                dto.setSoluong(Integer.parseInt(rs.getString("SoLuong")));
                dto.setTinhtrang(rs.getString("TinhTrang"));
                ds.add(dto);
            }
            return ds;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            pro.close();
        }
        return ds;
    }

    public SANPHAMDTO timsanphamcoma(int ma) {
        DataProvider pro = new DataProvider();
        SANPHAMDTO dto = new SANPHAMDTO();
        ResultSet rs = null;
        try {
            pro.open();
            String query = "SELECT * FROM SANPHAM WHERE MaSP =" + ma;
            rs = pro.excutequery(query);
            if (rs.next()) {
                dto.setMasp(Integer.parseInt(rs.getString("MaSP")));
                dto.setTensp(rs.getString("TenSP"));
                dto.setDongia(Integer.parseInt(rs.getString("DonGia")));
                dto.setMaloai(Integer.parseInt(rs.getString("MaLoai")));
                dto.setSoluong(Integer.parseInt(rs.getString("SoLuong")));
                dto.setTinhtrang(rs.getString("TinhTrang"));
            }
            return dto;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            pro.close();
        }
        return dto;
    }

    public ArrayList<SANPHAMDTO> laydanhsachsanpham() {
        DataProvider pro = new DataProvider();
        ArrayList<SANPHAMDTO> ds = new ArrayList<SANPHAMDTO>();
        ResultSet rs = null;
        try {
            pro.open();
            String query = "SELECT * FROM SANPHAM";
            rs = pro.excutequery(query);
            while (rs.next()) {
                SANPHAMDTO dto = new SANPHAMDTO();
                dto.setMasp(Integer.parseInt(rs.getString("MaSP")));
                dto.setTensp(rs.getString("TenSP"));
                dto.setDongia(Integer.parseInt(rs.getString("DonGia")));
                dto.setMaloai(Integer.parseInt(rs.getString("MaLoai")));
                dto.setSoluong(Integer.parseInt(rs.getString("SoLuong")));
                dto.setTinhtrang(rs.getString("TinhTrang"));
                ds.add(dto);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            pro.close();
        }
        return ds;
    }

    public boolean themsanpham(SANPHAMDTO dto) {
        DataProvider pro = new DataProvider();
        int kq;
        try {
            pro.open();
            String query = "INSERT INTO SanPham(TenSP,DonGia,MaLoai,SoLuong,TinhTrang) VALUES(N'"
                    + dto.getTensp() + "',"
                    + dto.getDongia() + ","
                    + dto.getMaloai() + ","
                    + dto.getSoluong() + ",N'"
                    + dto.getTinhtrang() + "')";
            kq = pro.excuteupdate(query);
            if (kq >= 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }
    
    
    public boolean xoasanphamcoma(int ma) {
        DataProvider pro = new DataProvider();
        int kq;
        try {
            pro.open();
            String query = "DELETE FROM SanPham WHERE MaSP = " + ma;
            kq = pro.excuteupdate(query);
            if (kq >= 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean xoasanpham(SANPHAMDTO dto) {
        DataProvider pro = new DataProvider();
        int kq;
        try {
            pro.open();
            String query = "DELETE FROM SanPham WHERE MaSP = " + dto.getMasp();
            kq = pro.excuteupdate(query);
            if (kq >= 0) {
                return true;
            }
        } catch (Exception e) {
           e.printStackTrace();
        }
        return false;
    }

    public boolean suasanpham(SANPHAMDTO dto)  {
        DataProvider pro = new DataProvider();
        int kq;
        try {
            pro.open();
            String query = "UPDATE SanPham set "
                    + "TenSP='" + dto.getTensp()
                    + "',DonGia=N'" + dto.getDongia()
                    + "',MaLoai='" + dto.getMaloai()
                    + "',SoLuong='" + dto.getSoluong()
                    + "',TinhTrang='" + dto.getTinhtrang() + "' where MaSP= " + dto.getMasp();
            kq = pro.excuteupdate(query);
            if (kq >= 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }
}
